using System;
using System.Data;
using System.IO;
using System.Collections.Generic;
using System.Text;

namespace SistemaLib.sipe.util.io
{
    public class ExcelToDataSet
    {
        public static DataSet GetDataSet(string caminoCsv)
        {
            DataSet ds = new DataSet();
            FileInfo fi = new FileInfo(caminoCsv);

            writeSchema(fi);

            /* You can get connected to driver either by using
            DSN or connection string. Create a connection string
            as below, if you want to use DSN less connection.
            The DBQ attribute sets the path of directory which 
            contains CSV files*/

            string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + fi.DirectoryName + ";Extensions=asc,csv,tab,txt;Persist Security Info=False;Readonly=False;";

            string sql_select;
            System.Data.Odbc.OdbcConnection conn;

            //Create connection to CSV file
            conn = new System.Data.Odbc.OdbcConnection(strConnString.Trim());

            //Open the connection 
            conn.Open();

            //Fetch records from CSV
            sql_select = "select * from [" + fi.Name + "]";

            System.Data.Odbc.OdbcDataAdapter obj_oledb_da = new System.Data.Odbc.OdbcDataAdapter(sql_select, conn);
            //Fill dataset with the records from CSV file

            obj_oledb_da.Fill(ds, "Medidas");


            conn.Close();

            return ds;

        }

        private static void writeSchema(FileInfo fi)
        {
            string strFormat = "Delimited(;)";

            FileStream fsOutput = new FileStream(fi.DirectoryName + "\\schema.ini", FileMode.Create, FileAccess.Write);
            StreamWriter srOutput = new StreamWriter(fsOutput);
            string s1, s2, s3, s4, s5;
            s1 = "[" + fi.Name + "]";
            s2 = "ColNameHeader=true";
            s3 = "Format=" + strFormat;
            s4 = "MaxScanRows=25";
            s5 = "CharacterSet=OEM";
            srOutput.WriteLine(s1.ToString() + '\n' + s2.ToString() + '\n' + s3.ToString() + '\n' + s4.ToString() + '\n' + s5.ToString());
            srOutput.Close();
            fsOutput.Close();

        }

    }
}
